﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace OraFolio.Gates
{
    using ClassFolio.Model;
    using System.Data.OracleClient;
    using System.Data;
    using System.Globalization;
    using ClassFolio;
    
    public class OraRoleGate : IRoleGate
    {
        public void RemoveRole(int activeUserId, int roleId)
        {
            OraDB.CheckConnection();
	        string sql = "begin folio.users_pkg.role_del(:1, :2); end;";
            OracleCommand cmd = new OracleCommand(sql, OraDB.Connection, OraDB.Transaction);
            cmd.Parameters.Add("1", OracleType.Number).Direction = ParameterDirection.Input;
            cmd.Parameters["1"].Value = activeUserId;
            cmd.Parameters.Add("2", OracleType.Number).Direction = ParameterDirection.Input;
            cmd.Parameters["2"].Value = roleId;
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception err)
            {
                throw new EFolioSource(0, MessageType.AllertLog, Messages.rl_remove_role, err);
            }
        }
		public bool RoleExist(int roleId)
        {
            OraDB.CheckConnection();
            string sql = " select count(*) cnt from roles where id = :1";
            OracleCommand cmd = new OracleCommand(sql, OraDB.Connection, OraDB.Transaction);
            cmd.Parameters.Add("1", OracleType.Number).Direction = ParameterDirection.Input;
            cmd.Parameters["1"].Value = roleId;

            OracleDataReader reader = cmd.ExecuteReader();
            try
            {
                if (reader.Read())
                    return Convert.ToInt32(reader["cnt"], CultureInfo.InvariantCulture) == 1;
                else
                    return false;
            }
            catch (Exception err)
            {
                throw new EFolioSource(0, MessageType.AllertLog, Messages.um_role_exist, err);
            }
            finally
            {
                reader.Close();
            }
        }
        public Role ApplyToDb(int activeUserId, Role role)
        {
			if (role.ID != 0)
				return RoleUpdate(activeUserId, role);
			else
				return RoleInsert(activeUserId, role);
        }
        public Role RefreshFromDb(Role role)
        {
            OraDB.CheckConnection();
            string sql = " select r.name from folio.roles r where r.id = :1";
            OracleCommand cmd = new OracleCommand(sql, OraDB.Connection, OraDB.Transaction);
            cmd.Parameters.Add("1", OracleType.Number).Direction = ParameterDirection.Input;
            cmd.Parameters["1"].Value = role.ID;

            OracleDataReader reader = cmd.ExecuteReader();
            try
            {
                while (reader.Read())
                {
                    role.Name = reader["name"].ToString();
                }
				return role;
			}
            catch (Exception err)
            {
                throw new EFolioSource(0, MessageType.AllertLog, Messages.um_refresh, err);
            }
            finally
            {
                reader.Close();
            }
        }
        public List<int> GetActionList(int roleId) 
        {
            OraDB.CheckConnection();
            string sql = " begin :1 := folio.safety_pkg.role_actions_list(:2); end;";
            OracleCommand cmd = new OracleCommand(sql, OraDB.Connection, OraDB.Transaction);
            cmd.Parameters.Add("1", OracleType.VarChar, 1024).Direction = ParameterDirection.Output;
            cmd.Parameters.Add("2", OracleType.Number).Direction = ParameterDirection.Input;
            cmd.Parameters["2"].Value = roleId;
            try
            {
                List<int> listAction = new List<int>();
                cmd.ExecuteNonQuery();
                listAction.AddRange(cmd.Parameters["1"].Value.ToString().Split(';')
                    .Where(i => !String.IsNullOrEmpty(i)).Select(i => Convert.ToInt32(i.Trim(), CultureInfo.InvariantCulture)).ToArray());
                return listAction;
            }
            catch (Exception err)
            {
                throw new EFolioSource(0, MessageType.AllertLog, Messages.rl_get_action, err);
            }
        }
        public void RoleAssignAction(int activeUserId, int roleId, int actionId)
        {
            OraDB.CheckConnection();
	        string sql = "begin folio.users_pkg.role_add_action(:1, :2, :3); end;";
            OracleCommand cmd = new OracleCommand(sql, OraDB.Connection, OraDB.Transaction);
            cmd.Parameters.Add("1", OracleType.Number).Direction = ParameterDirection.Input;
            cmd.Parameters["1"].Value = activeUserId;
            cmd.Parameters.Add("2", OracleType.Number).Direction = ParameterDirection.Input;
            cmd.Parameters["2"].Value = roleId;
            cmd.Parameters.Add("3", OracleType.Number).Direction = ParameterDirection.Input;
            cmd.Parameters["3"].Value = actionId;
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception err)
            {
                throw new EFolioSource(0, MessageType.AllertLog, Messages.rl_assign_action, err);
            }
        }
        public void RoleAssignActionInherit(int activeUserId, int roleId, int actionId)
        {
            OraDB.CheckConnection();
            string sql = "begin folio.users_pkg.role_add_action_inherit(:1, :2, :3); end;";
            OracleCommand cmd = new OracleCommand(sql, OraDB.Connection, OraDB.Transaction);
            cmd.Parameters.Add("1", OracleType.Number).Direction = ParameterDirection.Input;
            cmd.Parameters["1"].Value = activeUserId;
            cmd.Parameters.Add("2", OracleType.Number).Direction = ParameterDirection.Input;
            cmd.Parameters["2"].Value = roleId;
            cmd.Parameters.Add("3", OracleType.Number).Direction = ParameterDirection.Input;
            cmd.Parameters["3"].Value = actionId;
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception err)
            {
                throw new EFolioSource(0, MessageType.AllertLog, Messages.rl_assign_iherit_role, err);
            }
        }
        public void RoleRemoveAction(int activeUserId, int roleId, int actionId)
        {
            OraDB.CheckConnection();
            string sql = "begin folio.users_pkg.role_remove_action(:1, :2, :3); end;";
            OracleCommand cmd = new OracleCommand(sql, OraDB.Connection, OraDB.Transaction);
            cmd.Parameters.Add("1", OracleType.Number).Direction = ParameterDirection.Input;
            cmd.Parameters["1"].Value = activeUserId;
            cmd.Parameters.Add("2", OracleType.Number).Direction = ParameterDirection.Input;
            cmd.Parameters["2"].Value = roleId;
            cmd.Parameters.Add("3", OracleType.Number).Direction = ParameterDirection.Input;
            cmd.Parameters["3"].Value = actionId;
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception err)
            {
                throw new EFolioSource(0, MessageType.AllertLog, Messages.rl_remove_action, err);
            }

		}

		// Private
		private static Role RoleInsert(int activeUserId, Role role)
		{
			OraDB.CheckConnection();
			string sql = "begin :1 := folio.users_pkg.role_create(:2, :3); end;";
			OracleCommand cmd = new OracleCommand(sql, OraDB.Connection, OraDB.Transaction);
			cmd.Parameters.Add("1", OracleType.Number).Direction = ParameterDirection.Output;
			cmd.Parameters.Add("2", OracleType.Number).Direction = ParameterDirection.Input;
			cmd.Parameters["2"].Value = activeUserId;
			cmd.Parameters.Add("3", OracleType.VarChar).Direction = ParameterDirection.Input;
			cmd.Parameters["3"].Value = role.Name;

			try
			{
				cmd.ExecuteNonQuery();
				return Role.LoadRole(Convert.ToInt32(cmd.Parameters["1"].Value, CultureInfo.InvariantCulture));
			}
			catch (Exception err)
			{
				throw new EFolioSource(0, MessageType.AllertLog, Messages.rl_insert, err);
			}
		}
		private static Role RoleUpdate(int activeUserId, Role role)
		{
			OraDB.CheckConnection();
			string sql = "begin folio.users_pkg.role_upd(:1, :2, :3); end;";
			OracleCommand cmd = new OracleCommand(sql, OraDB.Connection, OraDB.Transaction);
			cmd.Parameters.Add("1", OracleType.Number).Direction = ParameterDirection.Input;
			cmd.Parameters["1"].Value = activeUserId;
			cmd.Parameters.Add("2", OracleType.Number).Direction = ParameterDirection.Input;
			cmd.Parameters["2"].Value = role.ID;
			cmd.Parameters.Add("3", OracleType.VarChar).Direction = ParameterDirection.Input;
			cmd.Parameters["3"].Value = role.Name;
			try
			{
				cmd.ExecuteNonQuery();
				return role;
			}
			catch (Exception err)
			{
				throw new EFolioSource(0, MessageType.AllertLog, Messages.rl_update, err);
			}
		}
	}
}
